package com.infore.statisticsAndAlarm.repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.infore.statisticsAndAlarm.entity.HourDataAir;

@Repository
public interface IHourDataAirRepository
		extends JpaRepository<HourDataAir, String>, JpaSpecificationExecutor<HourDataAir> {

	/**查询最新一条数据**/
	@Query(value="SELECT * FROM ( " + 
			" SELECT ROWNUM NO, c.mn, c.data_time, c.PM25, c.PM10, c.SO2, c.NO2, c.CO, c.O3 " + 
			" FROM ( SELECT ROW_NUMBER () OVER ( PARTITION BY r.mn ORDER BY r.mn DESC ) rn, " + 
			" r.mn, r.data_time, r.PM25, r.PM10, r.SO2, r.NO2, r.CO, r.O3 " + 
			" FROM ( select t.mn,t.data_time, " + 
			" sum(decode(t.factor_code, 'PM25', avg_value,null)) as PM25, " + 
			" sum(decode(t.factor_code, 'PM10', avg_value,null)) as PM10, " + 
			" sum(decode(t.factor_code, 'SO2', avg_value,null)) as SO2, " + 
			" sum(decode(t.factor_code, 'NO2', avg_value,null)) as NO2, " + 
			" sum(decode(t.factor_code, 'CO', avg_value,null)) as CO, " + 
			" sum(decode(t.factor_code, 'O3', avg_value,null)) as O3 " + 
			"from HOUR_DATA_AIR t WHERE t.mn IN (:mnsQuery )" + 
			"and t.DATA_TIME BETWEEN to_date(:start,'yyyy-mm-dd') "+
			"and to_date(:end,'yyyy-mm-dd hh24:mi') " + 
			"group by t.mn,t.data_time ORDER BY t.data_time DESC ) r ) c " + 
			"WHERE c.rn = 1 ) table_alias",nativeQuery=true)
	List<Map<String, Object>> queryNewData(@Param("mnsQuery")List<String> mnsQuery,@Param("start") String start,
			@Param("end") String end);
	
	@Query(value="select * from hour_data_air " + 
			" where data_time >= to_date(:startTime,'yyyy-mm-dd hh24:mi:ss') and "+ 
			"data_time <= to_date(:endTime,'yyyy-mm-dd hh24:mi:ss')",nativeQuery=true)
	List<HourDataAir> queryByDataTimeBetween(@Param("startTime")String  startTime,@Param("endTime")String endTime);
}
